limiting join results
От | Elaine Lindelef |
---|---|
Тема | limiting join results |
Дата | |
Msg-id | v04210109b9f9e66fb35e@[172.16.2.101] обсуждение исходный текст |
Ответы |
Re: limiting join results
Re: limiting join results |
Список | pgsql-general |
I am doing a query with a 3-way join. The join and select are working fine. However, what I want is to select only the row with the smallest timediff for each distinct t1.date. This is the query (simplified): select t1.date, t1.parent, t1.id, t2.id, t3.id, t3.date, (t3.date - t1.date) as timediff from (t1 LEFT JOIN t2 ON t1.parent = t2.id) LEFT JOIN t3 ON t2.page = t3.page where t3.date < t1.date and t3.event_type = 'page' and t1.user_id = '61516' and order by t1.date, timediff; Here are my results: t1.date | parent | t1.id | t2.id | t3.id | t3.date | timediff ------------------------+--------+--------+--------+--------+--------- ---------------+---------- 2002-11-14 14:46:33-08 | 102846 | 100918 | 102846 | 102845 | 2002-11-14 14:46:11-08 | 00:00:22 2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102855 | 2002-11-14 15:33:50-08 | 00:00:11 2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102853 | 2002-11-14 15:33:40-08 | 00:00:21 2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102847 | 2002-11-14 14:46:35-08 | 00:47:26 2002-11-14 15:34:01-08 | 102856 | 100921 | 102856 | 102845 | 2002-11-14 14:46:11-08 | 00:47:50 (5 rows) What I want are only the first two rows. However, I don't know how many distinct t1.date values I will have. Using DISTINCT doesn't seem to change the output, and I'm not convinced it would keep the correct row if it did. My normal habit is to clean up the results in perl, but it seems to me that I should be able to do it in the SQL query and be a bit cleaner. Thank you for your assistance. Elaine Lindelef
В списке pgsql-general по дате отправления: